package com.gohi.main.store.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.RowSet;

import org.apache.log4j.Logger;
import org.hibernate.TransactionException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;




import com.gohi.core.service.impl.DefaultService;
import com.gohi.main.user.hbm.ShopgooOrder;
import com.gohi.main.util.BdMapUtils;
import com.gohi.main.util.MD5;
import com.gohi.main.util.MsgUtils;
import com.gohi.main.util.StringUtil;
import com.gohi.main.util.global.GloblConfig;
import com.gohi.main.wxapi.process.MsgXmlUtil;
import com.gohi.main.wxapi.vo.Template;
import com.gohi.main.wxapi.vo.TemplateData;
import com.gohi.main.wxpay.api.WxPayUtil;
import com.gohi.main.store.action.StoreAction;
import com.gohi.main.store.hbm.ShopgooAppointmentCan;
import com.gohi.main.store.hbm.ShopgooAppointmentT;
import com.gohi.main.store.hbm.ShopgooComment;
import com.gohi.main.store.hbm.jdbcMapper.CommentMapper;
import com.gohi.main.store.service.ICommentService;

import com.mysql.jdbc.Statement;

public class CommentServiceImpl extends DefaultService<Object, String> implements
	ICommentService {
	Logger logger = Logger.getLogger(CommentServiceImpl.class);
	@SuppressWarnings("unchecked")
	@Override
	public List<ShopgooComment> getCommentByType(String types, String id)
			throws TransactionException {
		StringBuffer buf =new StringBuffer("select  ent.store_id, ent.member_id, ent.comment,ent.photo,ent.add_time,ent.fav_num, ent.order_id, ent.beautician_id, ent.beautician_name,ent.beautician_score, ent.store_score, ent.is_good, ent.item_score,mem.avatar,mem.nickname from  shopgoo_comment as ent , shopgoo_member as mem  where 1=1");
		//商铺ID
		if(types.equals("1")){
			buf.append(" and ent.store_id="+id);
		}
		//服务查询
		if(types.equals("2")){
			buf.append(" and ent.item_id="+id);
		}
		
		//技师的ID 查询
		if(types.equals("3")){
			buf.append(" and ent.beautician_id="+id);
		}
		buf.append("  and mem.member_id=ent.member_id order by add_time desc");
		
		JdbcTemplate jdbc = getDAO().getJdbcTemplate();
	//	System.err.println(buf.toString());
		return jdbc.query(buf.toString(), new CommentMapper());

	}

	@Override
	public List<ShopgooOrder> getOrdersByUser(int userid,String type){
		String sql ="";
		if(type==null || type.equals("1")){
			//查询全部
			sql="select o.* from shopgoo_order as o  where   o.state!=1 and  o.member_id ="+userid+" ORDER BY  o.add_time desc";
		//查询未消费的
		}else if(type.equals("2")){
			sql ="select o.* from shopgoo_order as o   where   o.state!=3 and o.state!=1  and    o.member_id ="+userid+"  ORDER BY  o.add_time desc ";
		//已消费
		}else if(type.equals("3")){
			sql ="select o.* from shopgoo_order as o   where    o.state=3  and   o.member_id ="+userid+" ORDER BY  o.add_time desc";
		//查询没评价的
		}else if(type.equals("4")){
			sql ="select o.* from shopgoo_order as o   where  o.state!=1   and   o.member_id ="+userid+" and o.state=3 and o.comment=1 ORDER BY  o.add_time desc ";
		}
		
		//System.err.println(sql);
		List<ShopgooOrder> list = new  ArrayList<ShopgooOrder>();	
		SqlRowSet rs= getDAO().getJdbcTemplate().queryForRowSet(sql);
		ShopgooOrder o =null;
		while(rs.next()){
			o=new ShopgooOrder();
			o.setAdd_time(rs.getInt("add_time"));//订单提交时间
			o.setBeautician_id(rs.getInt("beautician_id"));//技师ID
			o.setBeautician_name(rs.getString("beautician_name"));//技师名字
			o.setComment(rs.getInt("comment"));//留言状态
			o.setItem_id(rs.getInt("item_id")); //服务的ID
			o.setItem_name(rs.getString("item_name"));//服务名称
			o.setMember_id(rs.getInt("member_id"));//会员的ID
			o.setMember_name(rs.getString("member_name"));//会员的名字
			o.setMobile(rs.getString("mobile"));//手机号码
			o.setOrder_id(rs.getInt("order_id"));//订单的ID
			o.setOrder_out(rs.getString("order_out"));//外部订单号
			o.setOrder_sn(rs.getString("order_sn"));//内部订单号
			o.setPay_type(rs.getInt("pay_type"));//支付类型
			o.setPrice(rs.getString("price"));//价格
	    	//	o.setStart_hour(StringUtil.parentSimple(rs.getInt("start_hour"), "yyyy-MM-dd HH:mm"));//套餐开始时间
			//System.err.println(rs.getString("state"));
			o.setState(rs.getInt("state"));//状态
			o.setStore_id(rs.getInt("store_id"));//商铺的ID
			o.setStore_name(rs.getString("store_name"));//商铺的名字
			o.setUse_time(rs.getInt("use_time")); //消费时间
	/*		String item = rs.getString("itemimg");
			String sp[] =item.split(",");
			o.setItemimg(GloblConfig.IMG_ITEMS+sp[0]);*/
			o.setItemimg(rs.getString("itemimg"));
			list.add(o);
		}
		
		return list;
	}
	
	@Override
	public List<ShopgooAppointmentCan> getBeauticianTime(String hour,
			String beautician_id) {
		String sql ="SELECT * FROM shopgoo_appointment_can where appt_date>=CURRENT_DATE  and appt_date<=ADDDATE(CURRENT_DATE,INTERVAL 3 DAY) AND (end_hour-start_hour)>="+hour+"*60  and beautician_id="+beautician_id+" order by appt_date asc,start_hour asc";
		SqlRowSet rs= getDAO().getJdbcTemplate().queryForRowSet(sql);
		List<ShopgooAppointmentCan> list = new ArrayList<ShopgooAppointmentCan>();
		while(rs.next()){
			ShopgooAppointmentCan  st =new ShopgooAppointmentCan();
			st.setAppt_date(rs.getString("appt_date"));
			st.setBeautician_id(rs.getString("beautician_id"));
			st.setBeautician_name(rs.getString("beautician_name"));
			st.setEnd_hour(rs.getInt("end_hour"));
			st.setStart_hour(rs.getInt("start_hour"));
			st.setStore_id(rs.getInt("store_id"));
			list.add(st);
		}
		//System.err.println(sql);
		return list;
	}

	@Override
	public String addOrders(final ShopgooOrder order) throws TransactionException {
		// TODO Auto-generated method stub
		final String strSql ="INSERT INTO shopgoo_order (order_sn, member_id, member_name, mobile, store_id, store_name, add_time,item_id, " +
				"item_name, number, price, order_out,  beautician_id, beautician_name,order_type,itemimg) "
				+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		   KeyHolder keyHolder = new GeneratedKeyHolder();
		 getDAO().getJdbcTemplate().update(new PreparedStatementCreator(){
			 			@Override
						public PreparedStatement createPreparedStatement(
								java.sql.Connection conn) throws SQLException {
			 				 int i = 0;
		                        java.sql.PreparedStatement ps = conn.prepareStatement(strSql, new String[] {"order_id"}); 
		                        ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
		                        ps.setString(++i, order.getOrder_sn());
		                        ps.setInt(++i, order.getMember_id());
		                        ps.setString(++i, order.getMember_name());
		                        ps.setString(++i, order.getMobile());
		                        ps.setInt(++i, order.getStore_id());
		                        ps.setString(++i, order.getStore_name());
		                        ps.setInt(++i,order.getAdd_time());
		                        ps.setInt(++i,order.getItem_id());
		                        ps.setString(++i, order.getItem_name());
		                        ps.setInt(++i, order.getNumber());
		                        ps.setDouble(++i, Double.parseDouble(order.getPrice()));
		                        ps.setString(++i, order.getOrder_out());
		                        ps.setInt(++i, order.getBeautician_id());
		                        ps.setString(++i, order.getBeautician_name());
		                        ps.setInt(++i, order.getOrder_type());
		                        ps.setString(++i, order.getItemimg());
		                      //  order_type
		                         return ps;
						}
	                },
	                keyHolder);
	    
	       // System.out.println("id = " + keyHolder.getKey().intValue());
	        return keyHolder.getKey().intValue()+"";
	
	}

	@Override
	public int addAppointment(ShopgooAppointmentT app) {
		// TODO Auto-generated method stub
		String sql ="INSERT INTO shopgoo_appointment_t(store_id, beautician_id, beautician_name, appt_date, start_hour, end_hour,state,src,order_id,taocan_id,taocantime) VALUES ('"+app.getStore_id()+"', '"+app.getBeautician_id()+"', '"
		+app.getBeautician_name()+"', '"+app.getAppt_date()+"', '"+app.getStart_hour()+"', '"+app.getEnd_hour()+"','2','1','"+app.getOrder_id()+"',"+app.getTaocan_id()+","+app.getTaocantime()+")";
		//System.err.println(sql);
		return getDAO().getJdbcTemplate().update(sql);
	}

	@Override
	public int changeOrders(String orderid, String paytype,String openid) {
		// TODO Auto-generated method stub
		//交易成功 需要做的步骤 
		//1修改订单的状态
		//2：修改预约时间的状态
		//3：拆分时间完毕
		ShopgooOrder order = queryOrderById(orderid);
		//支付类型为线上支付  ， 支付状态为2  
		if(order.getPay_type()==1 && order.getState()==2){
			//如果交易成功了 ，直接返回  
			return 1;
		}
		
		String sql ="";
		JdbcTemplate jdbc= this.getDAO().getJdbcTemplate();
		//int result=0;//受影响的行
		if(paytype.equals("2")){
		//线下付款
		sql="update shopgoo_order set state=5 ,pay_type=2 where order_id="+orderid;
		}else{
			//线上付款
			sql="update shopgoo_order set state=2,pay_type=1 where order_id="+orderid;
		}
		
		jdbc.execute(sql);//执行修改
		//###################################################################执行增加销售数量的
		sql ="update shopgoo_store set groupbuy_num=groupbuy_num+1 where store_id="+order.getStore_id();
		jdbc.execute(sql);//执行累计商铺的销售数量
		if(order.getOrder_type()==3){
			sql ="update shopgoo_specialoffer  set buyer_num=buyer_num+1 where id="+order.getItem_id();
		}else{
			sql ="update shopgoo_groupbuy  set buyer_num=buyer_num+1 where group_id="+order.getItem_id();
		}

		
		jdbc.execute(sql);//执行累计服务销售的数量
		

	//	System.err.println("技师的ID："+order.getBeautician_id());
		if(order.getBeautician_id()!=0){
			sql = "update shopgoo_beautician  set order_num=order_num+1 where beautician_id="+order.getBeautician_id();
			jdbc.execute(sql);//执行技师累计服务销售的数量
			//System.err.println("===================================================");
			//如果选择了技师  就要经行修改时间的操作 ===============================================================
			//修改那个预约时间的状态
			sql="update shopgoo_appointment_t set state=1 where order_id="+orderid;
			jdbc.execute(sql);//执行修改
			//下面开始拆分时间
			sql ="SELECT c.*,t.taocantime,t.start_hour,t.end_hour from shopgoo_appointment_t as t, shopgoo_appointment_can as c where t.order_id="+orderid+" and t.beautician_id=c.beautician_id   and  t.start_hour>=c.start_hour and t.end_hour<=c.end_hour";
			SqlRowSet rs = jdbc.queryForRowSet(sql);
			//System.err.println(sql);
		
			ShopgooAppointmentCan can =null;
			//读取时间
			while(rs.next()){
				can =new ShopgooAppointmentCan();
				can.setAppt_date(rs.getString("appt_date"));//日期
				can.setBeautician_id(rs.getString("beautician_id"));//技师的ID
				can.setBeautician_name(rs.getString("beautician_name"));//技师的名字
				can.setId(rs.getInt("id"));
				can.setTaocantime(rs.getInt("taocantime"));
				can.setStore_id(rs.getInt("store_id"));
				can.setStart_hour(rs.getInt("start_hour"));//开始时间
				can.setEnd_hour(rs.getInt("end_hour"));	//结束时间
			   //System.err.println(rs.getInt(10));
				can.setTcend_hour(rs.getInt(10));//套餐预约结束时间
				can.setTcstart_hour(rs.getInt(9));//套餐预约开始的时间
			}
			
			
			if(can==null){
				logger.error("订单编号:"+orderid+"\t 预约时间失败！");
				return 0;
			}
			//如果预约时间等于开始时间
			int temptime = can.getTcstart_hour()+can.getTaocantime()*60;
			
			//System.err.println(can.getStart_hour()+"==="+can.getTcstart_hour());
			if(can.getStart_hour().equals(can.getTcstart_hour())){
				if(temptime==can.getEnd_hour()){
					//如果时间刚好相等直接干掉数据
					sql = "delete from shopgoo_appointment_can where id="+can.getId();
				}else{
				    sql = "update shopgoo_appointment_can  set start_hour ='"+temptime+"' where id="+can.getId();
				}
				jdbc.execute(sql);
			//预约时间加套餐时间等于结束时间
			}else if(can.getEnd_hour().equals(temptime)){
				//直接删除
				sql = "delete from shopgoo_appointment_can where id="+can.getId();
				jdbc.execute(sql);
			}else{
				//需要拆分两条数据出来了
				//第一步更新  开始时间为套餐的结束时间
				 sql = "update shopgoo_appointment_can set  start_hour  ='"+temptime+"' where id="+can.getId();
				 jdbc.execute(sql);
				//第二步插入一个新的数据  以开始时间到套餐的开始时间
				sql="INSERT INTO shopgoo_appointment_can (store_id, beautician_id, beautician_name, appt_date, start_hour, end_hour) " +
						"VALUES ('"+can.getStore_id()+"', '"+can.getBeautician_id()+"', '"+can.getBeautician_name()+"', '"+can.getAppt_date()+"', '"+can.getStart_hour()+"', '"+can.getTcstart_hour()+"')";
				 jdbc.execute(sql);
			}
			
			
		}
		//插入支付信息
		String pwd = MsgUtils.getpwd();
		sql ="INSERT INTO shopgoo_order_pwd( order_id, order_pwd) VALUES ('"+orderid+"', '"+pwd+"')";
		jdbc.execute(sql);
        //发送短信
	
		String text  =MsgUtils.getYuyue(order.getStore_name()+order.getItem_name(), pwd);
		MsgUtils.SendMsg(order.getMobile(),text);
		
		
		
		//发送模板消息
		
		Template t = new Template();
		Map<String, TemplateData> m = new HashMap<String, TemplateData>();
		m.put("product", new TemplateData(order.getItem_name()));

		m.put("first", new TemplateData("您已经成功预约，时间为："+order.getStart_hour()+" ，技师为："+order.getBeautician_name()));
		m.put("price", new TemplateData(order.getPrice()+"元"));
		m.put("time", new TemplateData(StringUtil.getDate("yyyy-MM-dd HH:mm:ss")));
		m.put("remark", new TemplateData("订单编号："+order.getOrder_out()+",消费码:"+pwd));
		t.setData(m);
		t.setUrl(GloblConfig.HOST_URL+"centent!orderdetail.do?id="+order.getOrder_id()+"&openid="+openid);
		t.setTemplate_id(GloblConfig.Tmplmsg_BuyOk);
		t.setTouser(openid);// 发送给谁
		MsgXmlUtil.sendTemplateMsg(t, WxPayUtil.getAccount()
				.getAccess_token());
		//System.err.println(s);
		//开始发送短信
		//System.err.println("完成！");
		//判断拆分时间
		
		return 1;
	}

	@Override
	public ShopgooOrder queryOrderById(String id) {
		String sql = "SELECT o.*,t.start_hour from  shopgoo_order as o, shopgoo_appointment_t as t  where  o.order_id ="+id+" and t.order_id=o.order_id";
		//System.err.println(sql);
		SqlRowSet rs= getDAO().getJdbcTemplate().queryForRowSet(sql);
		ShopgooOrder o =null;
		while(rs.next()){
			o=new ShopgooOrder();
			o.setAdd_time(rs.getInt("add_time"));//订单提交时间
			o.setBeautician_id(rs.getInt("beautician_id"));//技师ID
			o.setBeautician_name(rs.getString("beautician_name"));//技师名字
			o.setComment(rs.getInt("comment"));//留言状态
			o.setItem_id(rs.getInt("item_id")); //服务的ID
			o.setItem_name(rs.getString("item_name"));//服务名称
			o.setMember_id(rs.getInt("member_id"));//会员的ID
			o.setMember_name(rs.getString("member_name"));//会员的名字
			o.setMobile(rs.getString("mobile"));//手机号码
			o.setOrder_id(rs.getInt("order_id"));//订单的ID
			o.setOrder_out(rs.getString("order_out"));//外部订单号
			o.setOrder_sn(rs.getString("order_sn"));//内部订单号
			o.setPay_type(rs.getInt("pay_type"));//支付类型
			o.setPrice(rs.getString("price"));//价格
			o.setStart_hour(StringUtil.parentSimple(rs.getInt("start_hour"), "yyyy-MM-dd HH:mm"));//套餐开始时间
			//System.err.println(rs.getString("state"));
			o.setState(rs.getInt("state"));//状态
			o.setStore_id(rs.getInt("store_id"));//商铺的ID
			o.setStore_name(rs.getString("store_name"));//商铺的名字
			o.setUse_time(rs.getInt("use_time")); //消费时间
			System.err.println(rs.getString("order_type"));
			o.setOrder_type(rs.getInt("order_type"));
			
		}
		return o;
	}

	@Override
	public int addComment(ShopgooComment comm) {
		String sql ="INSERT INTO shopgoo_comment(store_id,store_name, member_id, member_name, comment,  add_time, order_id, beautician_id, beautician_name,beautician_score,store_score, is_good,item_score,item_id,comment_type)"
				+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		  Object[] params = new Object[] {
				  comm.getStore_id(),
				  comm.getStore_name(),
				  comm.getMember_id(),
				  comm.getMember_name(),
				  comm.getComment(),
				  StringUtil.getTimeSimple(),
				  comm.getOrder_id(),
				  comm.getBeautician_id(),
				  comm.getBeautician_name(),
				  comm.getBeautician_score(),
				  comm.getStore_score(),
				  comm.getIs_good(),
				  comm.getItem_score(),
				  comm.getItem_id(),
				  comm.getComment_type()
				  
				  
	};
		  
   	int result  =getDAO().getJdbcTemplate().update(sql,params);
 
   	if(comm.getComment_type().equals("1")){
   		//服务评论+1
   		sql ="update shopgoo_groupbuy set comment_count=comment_count+1 where group_id="+comm.getItem_id();
   	}else  if(comm.getComment_type().equals("3")){
     	//服务评论优惠信息
   		sql ="update shopgoo_specialoffer set comment_count=comment_count+1 where id="+comm.getItem_id();
   	}
	
	getDAO().getJdbcTemplate().update(sql);
	//店铺评论+1  修改商户积分
	sql="update shopgoo_store set comment_count=comment_count+1  ,  store_score=store_score+"+ comm.getStore_score()+"  where  store_id="+comm.getStore_id();
	getDAO().getJdbcTemplate().update(sql);
	if(comm.getIs_good()==1){
		//好评论
		sql ="update shopgoo_beautician set comment_count=comment_count+1,count_num=count_num+3,good_num=good_num+1  where beautician_id="+comm.getBeautician_id();
	}else{
		sql ="update shopgoo_beautician set comment_count=comment_count+1,count_num=count_num+3,bad_num=bad_num+1  where beautician_id="+comm.getBeautician_id();
	}
	getDAO().getJdbcTemplate().update(sql);
	//订单表示已经评论
	sql ="update shopgoo_order set comment=2 where order_id="+comm.getOrder_id();
	getDAO().getJdbcTemplate().update(sql);
	return result;
	}

	@Override
	public int checkOrderTime(String orderid) {
		// TODO Auto-generated method stub
		String sql ="SELECT count(*) as c  from shopgoo_appointment_t as t, shopgoo_appointment_can as c where t.order_id="+orderid+" and t.beautician_id=c.beautician_id   and  t.start_hour>=c.start_hour and t.end_hour<=c.end_hour";
		
		//System.err.println(sql);
		return getDAO().getJdbcTemplate().queryForInt(sql);
	}

	@Override
	public int chageOrderstet(String order, int state) {
		ShopgooOrder o  = queryOrderById(order);
		//修改订单状态为3 锁定
		getDAO().getJdbcTemplate().update("update  shopgoo_order_pwd set state=3 where order_id="+o.getOrder_id());
		
		//如果是线下支付直接返回  不在退款表里面插入数据
	    if(o!=null && o.getPay_type()==1){
			//执行插入到退款表的SQL
			String  sql  =  "INSERT INTO shopgoo_refund (order_id,order_sn, order_pwd, member_id, member_name, store_id, store_name, refund_price) " +
					"VALUES ('"+o.getOrder_id()+"', '"+o.getOrder_sn()+"',(select order_group_id from shopgoo_order_pwd where order_id="+o.getOrder_id()+"), '"+o.getMember_id()+"', '"+o.getMember_name()+"', '"+o.getStore_id()+"', '"+o.getStore_name()+"', '"+o.getPrice()+"')";
			//System.err.println(sql);
			getDAO().getJdbcTemplate().update(sql);
			return getDAO().getJdbcTemplate().update("update shopgoo_order set state ="+state+" where order_id="+order);
		}else{
			try {
			String result = BdMapUtils.get(GloblConfig.HOST_URL+"index.php?act=appointmentt&op=cancelOrder&order_id="+o.getOrder_id());
		    System.err.println(result);
			} catch (Exception e) {
				// TODO Auto-generated catch block
			}
			//直接取消订单
			return getDAO().getJdbcTemplate().update("update shopgoo_order set state =6 where order_id="+order);
		}

	}

	@Override
	public long queryOrderPwd(String order) {
		// TODO Auto-generated method stub
		//System.err.println("select order_pwd from shopgoo_order_pwd where order_id="+order);
		if(getDAO().getJdbcTemplate().queryForInt("select count(*) as c from shopgoo_order_pwd where order_id="+order)==0){
			return 0;
		}
		long pwd = getDAO().getJdbcTemplate().queryForLong("select order_pwd from shopgoo_order_pwd where order_id="+order +" LIMIT 1");
		//System.err.println(pwd);
		return pwd;
	}

	@Override
	public int reBindWxuser(String openid) {
		// TODO Auto-generated method stub
		//System.err.println("update shopgoo_wx_member set realid=null where id='"+openid+"'");
		return getDAO().getJdbcTemplate().update("update shopgoo_wx_member set realid=null where id='"+openid+"'");
	}



}
